package com.fau.ss.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.beans.BeanUtils;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.ss.entity.MedicalAdmin;
import com.fau.ss.entity.MedicalCall;
import com.fau.ss.entity.MedicalCallEnd;
import com.fau.ss.entity.MedicalCallPending;
import com.fau.ss.entity.MedicalCallTelInfo;
import com.fau.ss.entity.MedicalCallUntreated;
import com.fau.ss.entity.MedicalGroup;
import com.fau.ss.entity.MedicalKeyValue;
import com.fau.ss.entity.MedicalMediaInfo;
import com.fau.ss.entity.MedicalMedicineSort;
import com.fau.ss.entity.MedicalPlatform;
import com.fau.ss.entity.MedicalSsPurchase;
import com.fau.ss.service.IPurchasingPowerStaService;
import com.fau.ss.util.DateUtil;
import com.fau.ss.util.Page;
import com.fau.ss.util.ParamUtil;
import com.fau.ss.util.SystemConfig;

@Service
@Transactional(readOnly = true)
public class PurchasingPowerStaService extends BaseService implements
		IPurchasingPowerStaService {

	@Override
	public Page findPurPowerBasicByPage(MedicalSsPurchase purchase, MedicalAdmin admin, Map<String, Object> map) {
		
		StringBuilder statusSql = new StringBuilder("select a.name, a.customerId, a.sex, a.phoneNum, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? ");
		StringBuilder countSql = new StringBuilder("select count(distinct a.customerId) from MedicalSsPurchase a where a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		
		if(StringUtils.isNotBlank(purchase.getStartTime()) && StringUtils.isNotBlank(purchase.getEndTime())) {
			Date startTime = DateUtil.getDate(purchase.getStartTime());
			Date endTime = DateUtil.getDate(purchase.getEndTime());
			
			statusSql.append("and a.ordersDate between ? and ? ");
			countSql.append("and a.ordersDate between ? and ? ");
			
			objectList.add(startTime);
			objectList.add(endTime);
		}
		
		if(null != purchase.getStartMoney() && null != purchase.getEndMoney()) {
			BigDecimal startMoney = new BigDecimal(purchase.getStartMoney());
			BigDecimal endMoney = new BigDecimal(purchase.getEndMoney());
			statusSql.append("and a.ordersMoney between ? and ? ");
			countSql.append("and a.ordersMoney between ? and ? ");
			objectList.add(startMoney);
			objectList.add(endMoney);
		}
		
		statusSql.append("group by a.customerId order by sm desc ");
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map, "pageIndex", 1), ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalSsPurchase> purchaseList = new ArrayList<MedicalSsPurchase>();
		for(Object obj : page.getResult()) {
			MedicalSsPurchase bean = new MedicalSsPurchase();
			Object[] objBean =  (Object[]) obj;
			
			bean.setName(objBean[0].toString());
			bean.setCustomerId(objBean[1].toString());
			bean.setSex(objBean[2].toString());
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(admin.getEnterpriseId(), objBean[2].toString());
			bean.setSexValue(kv.getKeyValue());
			bean.setPhoneNum(objBean[3].toString());
			BigDecimal ordersMoney = new BigDecimal(objBean[4].toString());
			bean.setOrdersMoney(ordersMoney);
			purchaseList.add(bean);
		}
		page.setResult(purchaseList);
		
		return page;
	}

	@Override
	public Page findPurPowerMedicineByPage(MedicalSsPurchase purchase,
			MedicalAdmin admin, Map<String, Object> map) {
		
		StringBuilder statusSql = new StringBuilder("select a.name, a.customerId, a.sex, a.phoneNum, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? ");
		StringBuilder countSql = new StringBuilder("select count(distinct a.customerId) from MedicalSsPurchase a where a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(admin.getEnterpriseId());
		
		if(StringUtils.isNotBlank(purchase.getStartTime()) && StringUtils.isNotBlank(purchase.getEndTime())) {
			Date startTime = DateUtil.getDate(purchase.getStartTime());
			Date endTime = DateUtil.getDate(purchase.getEndTime());
			
			statusSql.append("and a.ordersDate between ? and ? ");
			countSql.append("and a.ordersDate between ? and ? ");
			
			objectList.add(startTime);
			objectList.add(endTime);
		}

		if(StringUtils.isNotBlank(purchase.getMedicineSortId())) {
			statusSql.append("and a.medicineSortId=? ");
			countSql.append("and a.medicineSortId=? ");
			objectList.add(purchase.getMedicineSortId());
		}
		
		statusSql.append("group by a.customerId order by sm desc ");
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map, "pageIndex", 1), ParamUtil.getInteger(map, "pageSize", 20), map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalSsPurchase> purchaseList = new ArrayList<MedicalSsPurchase>();
		for(Object obj : page.getResult()) {
			MedicalSsPurchase bean = new MedicalSsPurchase();
			Object[] objBean =  (Object[]) obj;
			
			bean.setName(objBean[0].toString());
			bean.setCustomerId(objBean[1].toString());
			bean.setSex(objBean[2].toString());
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(admin.getEnterpriseId(), objBean[2].toString());
			bean.setSexValue(kv.getKeyValue());
			bean.setPhoneNum(objBean[3].toString());
			BigDecimal ordersMoney = new BigDecimal(objBean[4].toString());
			bean.setOrdersMoney(ordersMoney);
			purchaseList.add(bean);
		}
		page.setResult(purchaseList);
		
		return page;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalMedicineSort> findMedicineSortList(MedicalAdmin admin) {
		return this.iBaseDao.getBySql("from MedicalMedicineSort a where a.hospitalId=? ", null, admin.getEnterpriseId());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> StatisticsByAgeRange(MedicalAdmin admin, MedicalSsPurchase purchase) {
		List<String> list = new ArrayList<String>(5);
		List<MedicalSsPurchase> beanList1 = new ArrayList<MedicalSsPurchase>();
		List<MedicalSsPurchase> beanList2 = new ArrayList<MedicalSsPurchase>();
		List<MedicalSsPurchase> beanList3 = new ArrayList<MedicalSsPurchase>();
		List<MedicalSsPurchase> beanList4 = new ArrayList<MedicalSsPurchase>();
		List<MedicalSsPurchase> beanList5 = new ArrayList<MedicalSsPurchase>();
		
		if(StringUtils.isNotBlank(purchase.getStartTime()) && StringUtils.isNotBlank(purchase.getEndTime())) {
			Date startTime = DateUtil.getDate(purchase.getStartTime());
			Date endTime = DateUtil.getDate(purchase.getEndTime());
			beanList1 = this.iBaseDao.getBySql("select a.age, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? and a.ordersDate between ? and ? group by a.customerId having a.age between ? and ? ",
					null, admin.getEnterpriseId(), startTime, endTime, 20, 30);
			beanList2 = this.iBaseDao.getBySql("select a.age, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? and a.ordersDate between ? and ? group by a.customerId having a.age between ? and ? ",
					null, admin.getEnterpriseId(), startTime, endTime, 31, 40);
			beanList3 = this.iBaseDao.getBySql("select a.age, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? and a.ordersDate between ? and ? group by a.customerId having a.age between ? and ? ",
					null, admin.getEnterpriseId(), startTime, endTime, 41, 50);
			beanList4 = this.iBaseDao.getBySql("select a.age, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? and a.ordersDate between ? and ? group by a.customerId having a.age between ? and ? ",
					null, admin.getEnterpriseId(), startTime, endTime, 51, 60);
			beanList5 = this.iBaseDao.getBySql("select a.age, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? and a.ordersDate between ? and ? group by a.customerId having a.age > ? ",
					null, admin.getEnterpriseId(), startTime, endTime, 60);
		}else {
			beanList1 = this.iBaseDao.getBySql("select a.age, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? group by a.customerId having a.age between ? and ? ",
					null, admin.getEnterpriseId(), 20, 30);
			beanList2 = this.iBaseDao.getBySql("select a.age, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? group by a.customerId having a.age between ? and ? ",
					null, admin.getEnterpriseId(), 31, 40);
			beanList3 = this.iBaseDao.getBySql("select a.age, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? group by a.customerId having a.age between ? and ? ",
					null, admin.getEnterpriseId(), 41, 50);
			beanList4 = this.iBaseDao.getBySql("select a.age, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? group by a.customerId having a.age between ? and ? ",
					null, admin.getEnterpriseId(), 51, 60);
			beanList5 = this.iBaseDao.getBySql("select a.age, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? group by a.customerId having a.age > ? ",
					null, admin.getEnterpriseId(), 60);
		}
		
		list.add(String.valueOf(beanList1.size()));
		list.add(String.valueOf(beanList2.size()));
		list.add(String.valueOf(beanList3.size()));
		list.add(String.valueOf(beanList4.size()));
		list.add(String.valueOf(beanList5.size()));
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> StatisticsByYear(MedicalAdmin admin, MedicalSsPurchase purchase) {
		List<String> dataList = new ArrayList<String>();
		
		for(int i=1 ;i<13; i++){
			List<MedicalSsPurchase> beanList = this.iBaseDao.getBySql("select a.ordersDate, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? and YEAR(a.ordersDate) = ? group by a.customerId having MONTH( a.ordersDate) = ? ",
					null, admin.getEnterpriseId(), Integer.parseInt(purchase.getStartTime()), i);
			dataList.add(String.valueOf(beanList.size()));
		}
		return dataList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> StatisticsByMonth(MedicalAdmin admin, MedicalSsPurchase purchase) {
		List<String> list = new ArrayList<String>(3);
		List<MedicalSsPurchase> beanList1 = this.iBaseDao.getBySql("select a.ordersDate, max(a.ordersMoney) from MedicalSsPurchase a where 1=1 and a.hospitalId=? and DATE_FORMAT(a.ordersDate,'%Y—%m') = ? group by a.customerId having DAY( a.ordersDate) between ? and ? ",
				null, admin.getEnterpriseId(), purchase.getStartTime(), 1, 10);
		List<MedicalSsPurchase> beanList2 = this.iBaseDao.getBySql("select a.ordersDate, max(a.ordersMoney) from MedicalSsPurchase a where 1=1 and a.hospitalId=? and DATE_FORMAT(a.ordersDate, '%Y—%m') = ? group by a.customerId having DAY(a.ordersDate) between ? and ? ",
				null, admin.getEnterpriseId(), purchase.getStartTime(), 11, 20);
		List<MedicalSsPurchase> beanList3 = this.iBaseDao.getBySql("select a.ordersDate, max(a.ordersMoney) from MedicalSsPurchase a where 1=1 and a.hospitalId=? and DATE_FORMAT(a.ordersDate, '%Y—%m') = ? group by a.customerId having DAY(a.ordersDate) > ? ",
				null, admin.getEnterpriseId(), purchase.getStartTime(), 20);
		list.add(String.valueOf(beanList1.size()));
		list.add(String.valueOf(beanList2.size()));
		list.add(String.valueOf(beanList3.size()));
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> StatisticsByLevel(MedicalAdmin admin, MedicalSsPurchase purchase) {
		Map<String, Object> map = new LinkedHashMap<String, Object>();
		List<MedicalKeyValue> keyValList = this.iBaseDao.findValueByType(admin.getEnterpriseId(), SystemConfig.CM_SYSTEM_TYPE, purchase.getKeyType());
		if(StringUtils.isNotBlank(purchase.getStartTime()) && StringUtils.isNotBlank(purchase.getEndTime())) {
			Date startTime = DateUtil.getDate(purchase.getStartTime());
			Date endTime = DateUtil.getDate(purchase.getEndTime());
			for(MedicalKeyValue keyVal : keyValList) {
				List<MedicalSsPurchase> beanList1 = this.iBaseDao.getBySql("select a.level, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? and a.ordersDate between ? and ? group by a.customerId having a.level = ? ",
						null, admin.getEnterpriseId(), startTime, endTime, keyVal.getKeyName());
				map.put(keyVal.getKeyValue(), beanList1.size());
			}
		}else {
			for(MedicalKeyValue keyVal : keyValList) {
				List<MedicalSsPurchase> beanList1 = this.iBaseDao.getBySql("select a.level, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? group by a.customerId having a.level = ? ",
						null, admin.getEnterpriseId(), keyVal.getKeyName());
				map.put(keyVal.getKeyValue(), beanList1.size());
			}
		}
		return map;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> StatisticsByCity(MedicalAdmin admin,
			MedicalSsPurchase purchase) {
		Map<String, Object> map = new LinkedHashMap<String, Object>();
		List<MedicalMediaInfo> mediaInfoList = this.iBaseDao.getBySql("from MedicalMediaInfo a where 1=1 and a.eid = ? group by a.cityId ", null, admin.getEnterpriseId());
		if(StringUtils.isNotBlank(purchase.getStartTime()) && StringUtils.isNotBlank(purchase.getEndTime())) {
			Date startTime = DateUtil.getDate(purchase.getStartTime());
			Date endTime = DateUtil.getDate(purchase.getEndTime());
			for(MedicalMediaInfo mediaInfo : mediaInfoList) {
				List<MedicalSsPurchase> beanList1 = this.iBaseDao.getBySql("select a.cityId, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? and a.ordersDate between ? and ? group by a.customerId having a.cityId = ? ",
						null, admin.getEnterpriseId(), startTime, endTime, mediaInfo.getCityId());
				String address = mediaInfo.getAddress().substring(mediaInfo.getAddress().indexOf("-")+1, mediaInfo.getAddress().lastIndexOf("-"));
				map.put(address, beanList1.size());
			}
		}else {
			for(MedicalMediaInfo mediaInfo : mediaInfoList) {
				List<MedicalSsPurchase> beanList1 = this.iBaseDao.getBySql("select a.cityId, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? group by a.customerId having a.cityId = ? ",
						null, admin.getEnterpriseId(), mediaInfo.getCityId());
				String address = mediaInfo.getAddress().substring(mediaInfo.getAddress().indexOf("-")+1, mediaInfo.getAddress().lastIndexOf("-"));
				map.put(address, beanList1.size());
			}
		}
		return map;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalPlatform> findCenterList(MedicalAdmin admin) {
		return this.iBaseDao.getBySql("from MedicalPlatform a where a.eid=? ", null, admin.getEnterpriseId());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalGroup> findGroupListByCid(String cId) {
		return this.iBaseDao.getBySql("from MedicalGroup a where a.platform.id=? ", null, cId);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalAdmin> findUserListByGid(String gId) {
		return this.iBaseDao.getBySql("from MedicalAdmin a where a.group.id=? ", null, gId);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void allocate(String phoneNum, String usersId) {
		String phoneNums[] = phoneNum.split(",");
		String userId[] = usersId.split(",");
		List<String> userIdList = new ArrayList<String>(Arrays.asList(userId));
		
		List<MedicalCallUntreated> callUntreatedList = new ArrayList<MedicalCallUntreated>();
		for(String pn : phoneNums) {
			MedicalCallUntreated callUntreated = null;
			MedicalCallTelInfo telInfo = (MedicalCallTelInfo) this.iBaseDao.getObjectByHql("from MedicalCallTelInfo a where a.phoneNum=? ", pn);
			//查询未分配任务
			MedicalCall call = (MedicalCall) this.iBaseDao.getObject(MedicalCall.class, telInfo.getCallId());
			if(null != call) {
				callUntreated = new MedicalCallUntreated();
				BeanUtils.copyProperties(call, callUntreated);
				this.iBaseDao.saveObject(callUntreated);
				this.iBaseDao.deleteObject(call);
			}
			// 未处理任务
			callUntreated = (MedicalCallUntreated) this.iBaseDao.getObject(MedicalCallUntreated.class, telInfo.getCallId());
			// 待处理任务
			MedicalCallPending callPending = (MedicalCallPending) this.iBaseDao.getObject(MedicalCallPending.class, telInfo.getCallId());
			if(null != callPending) {
				callUntreated = new MedicalCallUntreated();
				BeanUtils.copyProperties(callPending, callUntreated);
				this.iBaseDao.saveObject(callUntreated);
				this.iBaseDao.deleteObject(callPending);
			}
			// 已结束任务
			MedicalCallEnd callEnd = (MedicalCallEnd) this.iBaseDao.getObject(MedicalCallEnd.class, telInfo.getCallId());
			if(null != callEnd) {
				callUntreated = new MedicalCallUntreated();
				BeanUtils.copyProperties(callEnd, callUntreated);
				this.iBaseDao.saveObject(callUntreated);
				this.iBaseDao.deleteObject(callEnd);
			}
			callUntreatedList.add(callUntreated);
		}
		
		for(int i=0; i<callUntreatedList.size(); i++){
			MedicalCallUntreated callUntreated = callUntreatedList.get(i);
			MedicalAdmin handleUser = (MedicalAdmin) this.iBaseDao.getObject(MedicalAdmin.class, userIdList.get(i % userIdList.size()));
			callUntreated.setCenterId(handleUser.getGroup().getPlatform().getId());
			callUntreated.setGroupId(handleUser.getGroup().getId());
			callUntreated.setHandleUserId(handleUser.getId());
			callUntreated.setGrade(SystemConfig.CALL_TASK_GRADE_USER);
			callUntreated.setAllocationTime(new Date());
		}
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportBasicAll(MedicalSsPurchase purchase) throws Exception {
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		StringBuilder statusSql = new StringBuilder("select a.name, a.customerId, a.sex, a.phoneNum, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(user.getEnterpriseId());
		
		if(StringUtils.isNotBlank(purchase.getStartTime()) && StringUtils.isNotBlank(purchase.getEndTime())) {
			Date startTime = DateUtil.getDate(purchase.getStartTime());
			Date endTime = DateUtil.getDate(purchase.getEndTime());
			
			statusSql.append("and a.ordersDate between ? and ? ");
			objectList.add(startTime);
			objectList.add(endTime);
		}
		
		if(null != purchase.getStartMoney() && null != purchase.getEndMoney()) {
			BigDecimal startMoney = new BigDecimal(purchase.getStartMoney().toString());
			BigDecimal endMoney = new BigDecimal(purchase.getEndMoney().toString());
			statusSql.append("and a.ordersMoney between ? and ? ");
			objectList.add(startMoney);
			objectList.add(endMoney);
		}
		
		statusSql.append("group by a.customerId ");
		
		List<Object[]> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		int size = list.size();
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.PURCHASING_BASIC_EXPORT);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		
		for(int j = 0;j < size;j++){
			Object[] objBean = list.get(j);
			Row currentRow = sheet.createRow(j+1);
			currentRow.createCell(0).setCellValue(j+1);
			currentRow.createCell(1).setCellValue(objBean[0].toString());
			currentRow.createCell(2).setCellValue(objBean[4].toString()+"元");
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(user.getEnterpriseId(), objBean[2].toString());
			currentRow.createCell(3).setCellValue(kv.getKeyValue());
			currentRow.createCell(4).setCellValue(objBean[3].toString());
		}
		return workbook;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook medicineSortExportBasicAll(MedicalSsPurchase purchase) throws Exception {
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		
		StringBuilder statusSql = new StringBuilder("select a.name, a.customerId, a.sex, a.phoneNum, max(a.ordersMoney) as sm from MedicalSsPurchase a where 1=1 and a.hospitalId=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(user.getEnterpriseId());
		
		if(StringUtils.isNotBlank(purchase.getStartTime()) && StringUtils.isNotBlank(purchase.getEndTime())) {
			Date startTime = DateUtil.getDate(purchase.getStartTime());
			Date endTime = DateUtil.getDate(purchase.getEndTime());
			
			statusSql.append("and a.ordersDate between ? and ? ");
			objectList.add(startTime);
			objectList.add(endTime);
		}

		if(StringUtils.isNotBlank(purchase.getMedicineSortId())) {
			statusSql.append("and a.medicineSortId=? ");
			objectList.add(purchase.getMedicineSortId());
		}
		
		statusSql.append("group by a.customerId ");
		List<Object[]> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		int size = list.size();
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.PURCHASING_BASIC_EXPORT);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		
		for(int j = 0;j < size;j++){
			Object[] objBean = list.get(j);
			Row currentRow = sheet.createRow(j+1);
			currentRow.createCell(0).setCellValue(j+1);
			currentRow.createCell(1).setCellValue(objBean[0].toString());
			currentRow.createCell(2).setCellValue(objBean[4].toString()+"元");
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(user.getEnterpriseId(), objBean[2].toString());
			currentRow.createCell(3).setCellValue(kv.getKeyValue());
			currentRow.createCell(4).setCellValue(objBean[3].toString());
		}
		return workbook;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook buyFrequencyExportBasicAll(MedicalSsPurchase purchase) throws Exception {
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		StringBuilder statusSql = new StringBuilder("from MedicalSsPurchase a where a.hospitalId = ? "); 
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(user.getEnterpriseId());
		
		Date startTime = null;
		Date endTime = null;
		if(StringUtils.isNotBlank(purchase.getStartTime()) && StringUtils.isNotBlank(purchase.getEndTime())) {
			startTime = DateUtil.getDate(purchase.getStartTime());
			endTime = DateUtil.getDate(purchase.getEndTime());
			
			statusSql.append("and a.ordersDate between ? and ? ");
			objectList.add(startTime);
			objectList.add(endTime);
		}
		
		statusSql.append("group by a.customerId order by count(distinct a.ordersId) desc");
		
		List<MedicalSsPurchase> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		int size = list.size();
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.BUY_FREQUENCY_BASIC_EXPORT);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		
		StringBuilder hql = new StringBuilder();
		List<Object> objects = new ArrayList<Object>();
		for(int j = 0;j < size;j++){
			MedicalSsPurchase bean = list.get(j);
			Row currentRow = sheet.createRow(j+1);
			currentRow.createCell(0).setCellValue(j+1);
			currentRow.createCell(1).setCellValue(bean.getName());
			hql.append("select count(distinct a.ordersId) from MedicalSsPurchase a where a.customerId = ? ");
			objects.add(bean.getCustomerId());
			if(null != startTime && null != endTime){
				hql.append("and a.ordersDate between ? and ? ");
				objects.add(startTime);
				objects.add(endTime);
			}
			int countBuy = this.iBaseDao.getIntCount(hql.toString(), objects.toArray());
			currentRow.createCell(2).setCellValue(countBuy);
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(user.getEnterpriseId(), bean.getSex());
			currentRow.createCell(3).setCellValue(kv.getKeyValue());
			currentRow.createCell(4).setCellValue(bean.getPhoneNum());
			hql.delete( 0, hql.length() );
			objects.clear();
		}
		return workbook;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Workbook buyFrequencyMsExportBasicAll(MedicalSsPurchase purchase) throws Exception {
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		
		StringBuilder statusSql = new StringBuilder("from MedicalSsPurchase a where 1=1 and a.hospitalId = ? "); 
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(user.getEnterpriseId());
		
		Date startTime = null;
		Date endTime = null;
		if(StringUtils.isNotBlank(purchase.getStartTime()) && StringUtils.isNotBlank(purchase.getEndTime())) {
			startTime = DateUtil.getDate(purchase.getStartTime());
			endTime = DateUtil.getDate(purchase.getEndTime());
			statusSql.append(" and a.ordersDate between ? and ? ");
			objectList.add(startTime);
			objectList.add(endTime);
		}
		
		if(StringUtils.isNotBlank(purchase.getMedicineSortId())) {
			statusSql.append(" and a.medicineSortId = ? ");
			objectList.add(purchase.getMedicineSortId());
		}
		
		statusSql.append("group by a.customerId order by count(distinct a.ordersId) desc");
		
		List<MedicalSsPurchase> list = this.iBaseDao.getBySql(statusSql.toString(), null, objectList.toArray());
		int size = list.size();
		
		String AbsolutelyPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.BUY_FREQUENCY_BASIC_EXPORT);
		File file = new File(AbsolutelyPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}	
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();

		Sheet sheet = workbook.getSheetAt(0);
		
		StringBuilder hql = new StringBuilder();
		List<Object> objects = new ArrayList<Object>();
		for(int j = 0;j < size;j++){
			MedicalSsPurchase bean = list.get(j);
			Row currentRow = sheet.createRow(j+1);
			currentRow.createCell(0).setCellValue(j+1);
			currentRow.createCell(1).setCellValue(bean.getName());
			hql.append("select count(distinct a.ordersId) from MedicalSsPurchase a where a.customerId = ? ");
			objects.add(bean.getCustomerId());
			if(null != startTime && null != endTime){
				hql.append("and a.ordersDate between ? and ? ");
				objects.add(startTime);
				objects.add(endTime);
			}
			int countBuy = this.iBaseDao.getIntCount(hql.toString(), objects.toArray());
			currentRow.createCell(2).setCellValue(countBuy);
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(user.getEnterpriseId(), bean.getSex());
			currentRow.createCell(3).setCellValue(kv.getKeyValue());
			currentRow.createCell(4).setCellValue(bean.getPhoneNum());
			hql.delete( 0, hql.length());
			objects.clear();
		}
		return workbook;
	}
	
}
